import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.eclipse.swt.SWT;
import org.eclipse.swt.browser.Browser;
import org.eclipse.swt.browser.ProgressEvent;
import org.eclipse.swt.browser.ProgressListener;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.DateTime;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Event;
import org.eclipse.swt.widgets.Group;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Listener;
import org.eclipse.swt.widgets.MessageBox;
import org.eclipse.swt.widgets.ProgressBar;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Text;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;


public class ImporterWindow {
	static Display display = new Display();
	static Shell shell = new Shell(display, SWT.BORDER | SWT.CLOSE | SWT.MIN);

	static Label lblProjectName = new Label(shell, SWT.HORIZONTAL);
	static Text txtProjectName = new Text(shell, SWT.SINGLE | SWT.BORDER);
	static Label lblMonth = new Label(shell, SWT.HORIZONTAL);
	static DateTime dtMonth = new DateTime(shell, SWT.DATE | SWT.BORDER | SWT.SHORT);
		
	static Group comSQLServer = new Group(shell, SWT.NONE);
	static Label lblSQLDomain = new Label(comSQLServer, SWT.HORIZONTAL);
	static Text txtSQLDomain = new Text(comSQLServer, SWT.SINGLE | SWT.BORDER);
	static Label lblSQLUsername = new Label(comSQLServer, SWT.HORIZONTAL);
	static Text txtSQLUsername = new Text(comSQLServer, SWT.SINGLE | SWT.BORDER);
	static Label lblSQLPassword = new Label(comSQLServer, SWT.HORIZONTAL);
	static Text txtSQLPassword = new Text(comSQLServer, SWT.SINGLE | SWT.BORDER | SWT.PASSWORD);
	
	static Group comSFServer = new Group(shell, SWT.NONE);
	static Label lblSFUsername = new Label(comSFServer, SWT.HORIZONTAL);
	static Text txtSFUsername = new Text(comSFServer, SWT.SINGLE | SWT.BORDER);
	static Label lblSFPassword = new Label(comSFServer, SWT.HORIZONTAL);
	static Text txtSFPassword = new Text(comSFServer, SWT.SINGLE | SWT.BORDER | SWT.PASSWORD);

	static ProgressBar pbProgress = new ProgressBar(shell, SWT.SMOOTH);
	static Label lblStatus = new Label(shell, SWT.HORIZONTAL);
	static Label lblPercent = new Label(shell, SWT.HORIZONTAL);
	static Browser browser = new Browser(shell, SWT.NONE);
	static Button btnDump = new Button(shell, SWT.PUSH);
	static Button btnDrop = new Button(shell, SWT.PUSH);
	
	static String dbName = "";
	static String sfUsername = "";
	static int projectID;
	static boolean allowExit = true;
	static int status;
	
	static Connection conn;
	static Statement s;
	static ResultSet rs = null;
		
	static Thread t = null;

	
	ImporterWindow() {
		shell.setText("Data Import: SourceForge");
		shell.setSize(400, 375);

		lblProjectName.setText("Project Name");
		lblProjectName.setSize(lblProjectName.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblProjectName.setLocation(20, 10);

		txtProjectName.setText("99999999999999999999999999999");
		txtProjectName.setSize(txtProjectName.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		txtProjectName.setLocation(30, 35);
		txtProjectName.setText("");
		
		lblMonth.setText("Month");
		lblMonth.setSize(lblMonth.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblMonth.setLocation(20, 70);

		dtMonth.setSize(dtMonth.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		dtMonth.setLocation(30, 95);
					
		comSFServer.setText("SourceForge Server");
		comSFServer.setLocation(10, 130);
		comSFServer.setSize(190, 150);
		
		lblSFUsername.setText("Domain");
		lblSFUsername.setSize(lblSFUsername.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblSFUsername.setLocation(10, 25);

		txtSFUsername.setText("999999999999999999");
		txtSFUsername.setSize(txtSFUsername.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		txtSFUsername.setLocation(20, 50);
		txtSFUsername.setText("");

		lblSFPassword.setText("Password");
		lblSFPassword.setSize(lblSFPassword.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblSFPassword.setLocation(10, 85);

		txtSFPassword.setText("999999999999999999");
		txtSFPassword.setSize(txtSFPassword.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		txtSFPassword.setLocation(20, 110);
		txtSFPassword.setText("");

		comSQLServer.setText("SQL Server");
		comSQLServer.setLocation(200, 70);
		comSQLServer.setSize(190, 210);
		
		lblSQLDomain.setText("Domain");
		lblSQLDomain.setSize(lblSQLDomain.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblSQLDomain.setLocation(10, 25);

		txtSQLDomain.setText("999999999999999999");
		txtSQLDomain.setSize(txtSQLDomain.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		txtSQLDomain.setLocation(20, 50);
		txtSQLDomain.setText("");

		lblSQLUsername.setText("Username");
		lblSQLUsername.setSize(lblSQLUsername.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblSQLUsername.setLocation(10, 85);

		txtSQLUsername.setText("999999999999999999");
		txtSQLUsername.setSize(txtSQLUsername.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		txtSQLUsername.setLocation(20, 110);
		txtSQLUsername.setText("");

		lblSQLPassword.setText("Password");
		lblSQLPassword.setSize(lblSQLPassword.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblSQLPassword.setLocation(10, 145);

		txtSQLPassword.setText("999999999999999999");
		txtSQLPassword.setSize(txtSQLPassword.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		txtSQLPassword.setLocation(20, 170);
		txtSQLPassword.setText("");

		lblStatus.setText("999999999999999999999999");
		lblStatus.setSize(lblStatus.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblStatus.setLocation(12, 290);
		lblStatus.setText("");

		lblPercent.setText("100%");
		lblPercent.setSize(lblPercent.computeSize(SWT.DEFAULT, SWT.DEFAULT));
		lblPercent.setLocation(245, 290);
		lblPercent.setText("");

		pbProgress.setSize(272, btnDump.computeSize(SWT.DEFAULT+100, SWT.DEFAULT).y-5);
		pbProgress.setLocation(10, 312);

		btnDump.setText("Dump");
		btnDump.setSize(btnDump.computeSize(SWT.DEFAULT+100, SWT.DEFAULT));
		btnDump.setLocation(380-btnDump.getSize().x, 310);
		btnDump.addListener(SWT.Selection, new Listener() {
			public void handleEvent(Event e) {
				try {
					if(!openSQLConnection()) {
						MessageBox msgbox = new MessageBox(shell, SWT.OK);
						msgbox.setText("Error");
						msgbox.setMessage("Cannot connect to database.");
						msgbox.open();
						return;
					}
						
					rs = s.executeQuery("SELECT * FROM project WHERE project_name='"+txtProjectName.getText()+"'");
					if(rs.first()) {
						MessageBox msgbox = new MessageBox(shell, SWT.YES | SWT.NO);
						msgbox.setText("Overwrite?");
						msgbox.setMessage("Data already exists for "+txtProjectName.getText()+".\nOverwrite?");
						if(msgbox.open() == SWT.NO)
							return;
						
						lblStatus.setText("Deleting Old Data");
						s.execute("DELETE FROM project WHERE project_name='"+txtProjectName.getText()+"'");
						s.execute("DELETE issue_status FROM issue_status LEFT JOIN issue ON issue_status.id WHERE issue.status_id IS NULL");
						s.execute("DELETE issue_resolution FROM issue_resolution LEFT JOIN issue ON issue_resolution.id WHERE issue.resolution_id IS NULL");
						s.execute("DELETE user FROM user LEFT JOIN issue ON user.user_id WHERE issue.submitted_by IS NULL");
					}
					pbProgress.setSelection(0);
					txtProjectName.setEnabled(false);
					txtSFPassword.setEnabled(false);
					txtSFUsername.setEnabled(false);
					txtSQLDomain.setEnabled(false);
					txtSQLPassword.setEnabled(false);
					txtSQLUsername.setEnabled(false);
					dtMonth.setEnabled(false);
					btnDump.setEnabled(false);
					btnDrop.setEnabled(false);

					allowExit = false;

					dbName = generateDBName();
					sfUsername = txtSFUsername.getText();
					status = 1;
					lblStatus.setText("Logging In");
					lblPercent.setText("0%");
					browser.setText(generateLogin());
				} catch (SQLException ex) {
					System.out.println("SQLException: " + ex.getMessage());
					System.out.println("SQLState: " + ex.getSQLState());
					System.out.println("VendorError: " + ex.getErrorCode());
				}
			};
		});

		btnDrop.setText("Drop Tables");
		btnDrop.setSize(btnDrop.computeSize(SWT.DEFAULT+100, SWT.DEFAULT));
		btnDrop.setLocation(380-btnDrop.getSize().x, 33);
		btnDrop.addListener(SWT.Selection, new Listener() {
			public void handleEvent(Event e) {
				try {
					MessageBox msgbox = new MessageBox(shell, SWT.YES | SWT.NO);
					msgbox.setText("Confirm?");
					msgbox.setMessage("Are you sure you want to drop the tables?");
					if(msgbox.open() == SWT.NO)
						return;
					msgbox.setMessage("Are you REALLY sure you want to drop the tables?");
					if(msgbox.open() == SWT.NO)
						return;
					
					if(!openSQLConnection()) {
						msgbox = new MessageBox(shell, SWT.OK);
						msgbox.setText("Error");
						msgbox.setMessage("Cannot connect to database.");
						msgbox.open();
						return;
					}
					
					lblStatus.setText("Deleting Tables");
					s.execute("DROP TABLE IF EXISTS issue, issue_status, issue_resolution, issue_category, issue_project_list, user, project");
					lblStatus.setText("Deleted Tables");
				} catch (SQLException ex) {
					System.out.println("SQLException: " + ex.getMessage());
					System.out.println("SQLState: " + ex.getSQLState());
					System.out.println("VendorError: " + ex.getErrorCode());
				}
			};
		});

		browser.addProgressListener(new ProgressListener() {
			public void changed(ProgressEvent event) {
			}
			public void completed(ProgressEvent event) {
				queryServer();
			}
		});
		browser.setVisible(false);
		
		shell.open();		
		
		shell.addListener (SWT.Close, new Listener() {
			public void handleEvent (Event e) {
				e.doit = allowExit;
			}
		});	
	}
	
	public static void main(String[] args) {
		@SuppressWarnings("unused")
		ImporterWindow iw = new ImporterWindow();
			while (!shell.isDisposed()) {
			if (!display.readAndDispatch())
				display.sleep();
		}
		display.dispose();
	};

	private static boolean openSQLConnection() {
		try {		
			conn = DriverManager.getConnection( "jdbc:mysql://"+txtSQLDomain.getText()+"?user="+txtSQLUsername.getText()+"&password="+txtSQLPassword.getText());
        	s = conn.createStatement();
        	s.execute("CREATE DATABASE IF NOT EXISTS IssuePlayer");
        	
			conn = DriverManager.getConnection( "jdbc:mysql://"+txtSQLDomain.getText()+"/issueplayer?user="+txtSQLUsername.getText()+"&password="+txtSQLPassword.getText());
        	s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        	
        	s.execute("CREATE TABLE IF NOT EXISTS project (project_id INTEGER NOT NULL, project_name TEXT, homepage TEXT, status TEXT, register_time INTEGER, PRIMARY KEY (project_id))");
        	s.execute("CREATE TABLE IF NOT EXISTS issue_project_list (project_issue_id INTEGER NOT NULL, project_id INTEGER, name TEXT, description TEXT, PRIMARY KEY (project_issue_id), FOREIGN KEY (project_id) REFERENCES project(project_id) ON DELETE CASCADE)");
        	s.execute("CREATE TABLE IF NOT EXISTS issue_resolution (id INTEGER NOT NULL, resolution_name TEXT, PRIMARY KEY (id))");
        	s.execute("CREATE TABLE IF NOT EXISTS issue_status (id INTEGER NOT NULL, status_name TEXT, PRIMARY KEY (id))");
        	s.execute("CREATE TABLE IF NOT EXISTS issue_category (id INTEGER NOT NULL, category_name TEXT, PRIMARY KEY (id))");
        	s.execute("CREATE TABLE IF NOT EXISTS user (user_id INTEGER NOT NULL, user_name TEXT, email TEXT, realname TEXT, status TEXT, PRIMARY KEY (user_id))");
        	s.execute("CREATE TABLE IF NOT EXISTS issue (issue_id INTEGER NOT NULL, project_issue_id INTEGER, status_id INTEGER, resolution_id INTEGER, category_id INTEGER, priority INTEGER, submitted_by INTEGER, assigned_to INTEGER, open_date INTEGER, close_date INTEGER, details TEXT, PRIMARY KEY (issue_id), FOREIGN KEY (project_issue_id) REFERENCES issue_project_list(project_issue_id) ON DELETE CASCADE, FOREIGN KEY (resolution_id) REFERENCES issue_resolution(id), FOREIGN KEY (status_id) REFERENCES issue_status(id), FOREIGN KEY (assigned_to) REFERENCES user(user_id), FOREIGN KEY (submitted_by) REFERENCES user(user_id), FOREIGN KEY (category_id) REFERENCES issue_category(id))");
        	
        	return true;
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return false;
		}		
	}

	private static String generateLogin() {
		String html;
		html = "<html><body onLoad=\"document.userlogin.submit()\">";
		html += "<form name=\"userlogin\" method=\"post\" action=\"http://zerlot.cse.nd.edu/mediawiki/index.php?title=Special:Userlogin&amp;action=submitlogin&amp;type=login\">";
		html += "<input type='text' class='loginText' name=\"wpName\" id=\"wpName1\" tabindex=\"1\" value=\""+txtSFUsername.getText()+"\" size='20' />";
		html += "<input type='password' class='loginPassword' name=\"wpPassword\" id=\"wpPassword1\" tabindex=\"2\" value=\""+txtSFPassword.getText()+"\" size='20' />";
		html += "<input type='checkbox' name=\"wpRemember\" tabindex=\"4\" value=\"1\" id=\"wpRemember\" />";
		html += "<input type='submit' name=\"wpLoginattempt\" id=\"wpLoginattempt\" tabindex=\"5\" value=\"Log in\" />";
		html += "<input type='submit' name=\"wpMailmypassword\" id=\"wpMailmypassword\" tabindex=\"6\" value=\"E-mail password\" />";
		html += "</form></body></html>";
		return html;
	}
		
	private static String generateDBName() {
		int queryMonth = dtMonth.getMonth()+1;
		int queryYear = dtMonth.getYear();
		queryYear = (queryYear > 2000 ? queryYear-2000 : queryYear-1900);
		String queryDate = "sf";
		
		if (queryMonth < 10)
			queryDate += "0" + queryMonth;
		else
			queryDate += queryMonth;
		
		if (queryYear < 10)
			queryDate += "0" + queryYear;
		else
			queryDate += queryYear;
		
		return queryDate;
	}

	private static void queryServer() {
		switch(status) {
		case 0:
			return;
		case 1:
			break;
		case 2:
			if (browser.getText().contains("Login error")) {
				MessageBox msgbox = new MessageBox(shell, SWT.OK);
				msgbox.setText("Error");
				msgbox.setMessage("Incorrect username or password.");
				msgbox.open();

				reset();
				return;
			}
						
			pbProgress.setSelection(10);
			lblStatus.setText("Logged In");
			lblPercent.setText("10%");
			browser.setText(generateProjectQuery());
			break;
		case 3:
			break;
		case 4:
			if (browser.getText().contains("There was an error in your query")) {
				MessageBox msgbox = new MessageBox(shell, SWT.OK);
				msgbox.setText("Error");
				msgbox.setMessage("Cannot retrieve the schema for the date entered.");
				msgbox.open();

				reset();
				return;
			}
			
			t = new Thread(new processProjectQuery());
			t.start();
			break;
		case 5:
			break;
		case 6:
			t = new Thread(new processSubmittedByQuery());
			t.start();
			break;
		case 7:
			break;
		case 8:
			t = new Thread(new processAssignedToQuery());
			t.start();
			break;
		case 9:
			break;
		case 10:
			t = new Thread(new processIssueQuery());
			t.start();
			break;
		}
		status++;
	}
	
	private static String generateProjectQuery() {
		String select = "group_id, group_name, homepage, status, register_time";
		String from = dbName+".groups";
		String where = "group_name='"+txtProjectName.getText()+"'";
		String queryhtml;

		queryhtml = "<html><body onLoad=\"document.query.submit()\">";
		queryhtml += "<FORM method=\"POST\" name=\"query\" action=\"http://zerlot.cse.nd.edu/cgi-bin/form.pl\">";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"uitems\" name=\"uitems\" value=\""+select+"\" size=62>";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"utables\" name=\"utables\" value=\""+from+"\" size=64>";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"uwhere\" name=\"uwhere\" value=\""+where+"\" size=62>";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\":\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\";\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\"#\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\",\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\"XML\" CHECKED>";
		queryhtml += "<INPUT type=\"radio\" name=\"append_query\" value=\"1\" CHECKED>";
		queryhtml += "<INPUT type=\"radio\" name=\"append_query\" value=\"0\">";
		queryhtml += "<INPUT type=\"submit\" value=\"Submit Query\">";
		queryhtml += "<INPUT type=\"reset\" value=\"Reset\">";
		queryhtml += "<INPUT type=\"button\" value=\"Clear\" onclick = \"clearIt()\">";
		queryhtml += "</FORM></body></html>";
	
		return queryhtml;	
	}
	
	private static String generateIssueQuery() {
		String select = "AGL.group_artifact_id, AGL.name AS artifact_group_list_name, AGL.description AS artifact_group_list_description, ART.artifact_id, ART.status_id, ART.category_id, ART.artifact_group_id, ART.resolution_id, ART.priority, ART.submitted_by, ART.assigned_to, ART.open_date, ART.close_date, ART.details, ARTS.status_name, ARTR.resolution_name";//, ARTC.category_name";
		String from = dbName+".artifact_group_list AGL, "+dbName+".artifact ART, "+dbName+".artifact_status ARTS, "+dbName+".artifact_resolution ARTR";//, "+dbName+".artifact_category ARTC";
		String where = "AGL.group_artifact_id=ART.group_artifact_id AND AGL.group_id="+projectID+" AND ARTS.id=ART.status_id AND ARTR.id=ART.resolution_id";// AND ARTC.group_artifact_id=AGL.group_artifact_id";
//		String select = "AGL.group_artifact_id, AGL.name AS artifact_group_list_name, AGL.description AS artifact_group_list_description, ART.artifact_id, ART.status_id, ART.category_id, ART.artifact_group_id, ART.resolution_id, ART.priority, ART.submitted_by, ART.assigned_to, ART.open_date, ART.close_date, ART.details";
//		String from = dbName+".artifact_group_list AGL, "+dbName+".artifact ART";
//		String where = "AGL.group_artifact_id=ART.group_artifact_id AND AGL.group_id="+projectID;
		String queryhtml;

		queryhtml = "<html><body onLoad=\"document.query.submit()\">";
		queryhtml += "<FORM method=\"POST\" name=\"query\" action=\"http://zerlot.cse.nd.edu/cgi-bin/form.pl\">";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"uitems\" name=\"uitems\" value=\""+select+"\" size=62>";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"utables\" name=\"utables\" value=\""+from+"\" size=64>";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"uwhere\" name=\"uwhere\" value=\""+where+"\" size=62>";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\":\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\";\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\"#\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\",\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\"XML\" CHECKED>";
		queryhtml += "<INPUT type=\"radio\" name=\"append_query\" value=\"1\" CHECKED>";
		queryhtml += "<INPUT type=\"radio\" name=\"append_query\" value=\"0\">";
		queryhtml += "<INPUT type=\"submit\" value=\"Submit Query\">";
		queryhtml += "<INPUT type=\"reset\" value=\"Reset\">";
		queryhtml += "<INPUT type=\"button\" value=\"Clear\" onclick = \"clearIt()\">";
		queryhtml += "</FORM></body></html>";
	
		return queryhtml;	
	}

	private static String generateSubmittedByQuery() {
		String select = "DISTINCT U.user_id, U.user_name, U.realname, U.status";
		String from = dbName+".artifact_group_list AGL, "+dbName+".artifact ART, "+dbName+".users U";
		String where = "AGL.group_artifact_id=ART.group_artifact_id AND ART.submitted_by=U.user_id AND AGL.group_id="+projectID;
		String queryhtml;

		queryhtml = "<html><body onLoad=\"document.query.submit()\">";
		queryhtml += "<FORM method=\"POST\" name=\"query\" action=\"http://zerlot.cse.nd.edu/cgi-bin/form.pl\">";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"uitems\" name=\"uitems\" value=\""+select+"\" size=62>";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"utables\" name=\"utables\" value=\""+from+"\" size=64>";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"uwhere\" name=\"uwhere\" value=\""+where+"\" size=62>";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\":\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\";\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\"#\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\",\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\"XML\" CHECKED>";
		queryhtml += "<INPUT type=\"radio\" name=\"append_query\" value=\"1\" CHECKED>";
		queryhtml += "<INPUT type=\"radio\" name=\"append_query\" value=\"0\">";
		queryhtml += "<INPUT type=\"submit\" value=\"Submit Query\">";
		queryhtml += "<INPUT type=\"reset\" value=\"Reset\">";
		queryhtml += "<INPUT type=\"button\" value=\"Clear\" onclick = \"clearIt()\">";
		queryhtml += "</FORM></body></html>";
	
		return queryhtml;	
	}

	private static String generateAssignedToQuery() {
		String select = "DISTINCT U.user_id, U.user_name, U.realname, U.status";
		String from = dbName+".artifact_group_list AGL, "+dbName+".artifact ART, "+dbName+".users U";
		String where = "AGL.group_artifact_id=ART.group_artifact_id AND ART.assigned_to=U.user_id AND AGL.group_id="+projectID;
		String queryhtml;

		queryhtml = "<html><body onLoad=\"document.query.submit()\">";
		queryhtml += "<FORM method=\"POST\" name=\"query\" action=\"http://zerlot.cse.nd.edu/cgi-bin/form.pl\">";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"uitems\" name=\"uitems\" value=\""+select+"\" size=62>";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"utables\" name=\"utables\" value=\""+from+"\" size=64>";
		queryhtml += "<INPUT autocomplete=\"off\" type=\"text\" id=\"uwhere\" name=\"uwhere\" value=\""+where+"\" size=62>";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\":\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\";\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\"#\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\",\">";
		queryhtml += "<INPUT type=\"radio\" name=\"useparator\" value=\"XML\" CHECKED>";
		queryhtml += "<INPUT type=\"radio\" name=\"append_query\" value=\"1\" CHECKED>";
		queryhtml += "<INPUT type=\"radio\" name=\"append_query\" value=\"0\">";
		queryhtml += "<INPUT type=\"submit\" value=\"Submit Query\">";
		queryhtml += "<INPUT type=\"reset\" value=\"Reset\">";
		queryhtml += "<INPUT type=\"button\" value=\"Clear\" onclick = \"clearIt()\">";
		queryhtml += "</FORM></body></html>";
	
		return queryhtml;	
	}

	private static class processProjectQuery implements Runnable {
		public void run() {
			try {
				InputStream in = new URL("http://zerlot.cse.nd.edu/qresult/"+sfUsername+"/"+sfUsername+".xml").openStream();
				DocumentBuilder dombuilder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
				Document dom = dombuilder.parse(in);
				
				if(!dom.getFirstChild().hasChildNodes()) { 
					display.syncExec(new Runnable() {
						public void run(){
							MessageBox msgbox = new MessageBox(shell, SWT.OK);
							msgbox.setText("Error");
							msgbox.setMessage("Cannot retrieve the schema for the project name entered.");
							msgbox.open();

							reset();
						}
					});							
					return;
				}
				
				NodeList root = dom.getFirstChild().getChildNodes();

				String project_name = "", homepage = "", project_status = "", register_time = "";
				for(Node node = root.item(1).getFirstChild(); node != null; node = node.getNextSibling()) {
					if(node.getNodeName() == "group_id") {
						projectID = new Integer(node.getTextContent());
					}
					else if(node.getNodeName() == "group_name") {
						project_name = node.getTextContent();
					}
					else if(node.getNodeName() == "homepage") {
						homepage = node.getTextContent();
					}
					else if(node.getNodeName() == "status") {
						project_status = node.getTextContent();
					}
					else if(node.getNodeName() == "register_time") {
						register_time = node.getTextContent();
					}
				}
			
				try {
					s.execute("INSERT INTO project (project_id, project_name, homepage, status, register_time) VALUES ("+projectID+","+project_name+","+homepage+","+project_status+","+register_time+")");
				} catch (SQLException e) {
					System.out.println("SQLException: " + e.getMessage());
					System.out.println("SQLState: " + e.getSQLState());
					System.out.println("VendorError: " + e.getErrorCode());
				}
			
			} catch (MalformedURLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (ParserConfigurationException e) {
				e.printStackTrace();
			} catch (SAXException e) {
				e.printStackTrace();
			}
			
			display.syncExec(new Runnable() {
				public void run(){
					pbProgress.setSelection(20);
					lblPercent.setText("20%");
					browser.setText(generateSubmittedByQuery());	
					lblStatus.setText("Querying Users");				      
				}
			});
		}
   }

	private static class processSubmittedByQuery implements Runnable {
		double pbinc, pbcur;
		String user_id;
		
		public void run() {
			try {
				InputStream in = new URL("http://zerlot.cse.nd.edu/qresult/"+sfUsername+"/"+sfUsername+".xml").openStream();
				DocumentBuilder dombuilder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
				Document dom = dombuilder.parse(in);
				NodeList root = dom.getFirstChild().getChildNodes();

				pbcur = 20;
				pbinc = 20.0 / (double)root.getLength();
				
				for(Node node1 = root.item(0); node1 != null; node1 = node1.getNextSibling()) {
					String user_name = "\"NULL\"", realname = "\"NULL\"", user_status = "\"NULL\"";
					user_id = "";
					pbcur += pbinc;

					for(Node node2 = node1.getFirstChild(); node2 != null; node2 = node2.getNextSibling()) {
						if(node2.getNodeName() == "user_id") {
							user_id = node2.getTextContent();
						}
						else if(node2.getNodeName() == "user_name") {
							user_name = removeQuotes(node2.getTextContent());
						}
						else if(node2.getNodeName() == "realname") {
							realname = removeQuotes(node2.getTextContent());
						}
						else if(node2.getNodeName() == "status") {
							user_status = removeQuotes(node2.getTextContent());
						}
					}
					
					if(user_id != "") {
						try {
							rs = s.executeQuery("SELECT user_id FROM user WHERE user_id="+user_id);
							if(rs.first())
								s.execute("UPDATE user SET user_name="+user_name+", realname="+realname+", status="+user_status+" WHERE user_id="+user_id);
							else
								s.execute("INSERT INTO user (user_id, user_name, realname, status) VALUES ("+user_id+","+user_name+","+realname+","+user_status+")");
						} catch (SQLException e) {
							System.out.println("SQLException: " + e.getMessage());
							System.out.println("SQLState: " + e.getSQLState());
							System.out.println("VendorError: " + e.getErrorCode());
							System.out.println("INSERT INTO user (user_id, user_name, realname, status) VALUES ("+user_id+","+user_name+","+realname+","+user_status+")");
							return;
						}
						display.syncExec(new Runnable() {
							public void run(){
								pbProgress.setSelection((int)pbcur);
								lblPercent.setText((int)pbcur + "%");
								lblStatus.setText("Processing User: " + user_id);
							}
						});							
					}
				}
			
			} catch (MalformedURLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (ParserConfigurationException e) {
				e.printStackTrace();
			} catch (SAXException e) {
				e.printStackTrace();
			}
		
			display.syncExec(new Runnable() {
				public void run(){
					browser.setText(generateAssignedToQuery());
					lblStatus.setText("Querying Users");
				}
			});							
		}
	}

	private static class processAssignedToQuery implements Runnable {
		double pbinc, pbcur;
		String user_id;
		
		public void run() {
			try {
				InputStream in = new URL("http://zerlot.cse.nd.edu/qresult/"+sfUsername+"/"+sfUsername+".xml").openStream();
				DocumentBuilder dombuilder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
				Document dom = dombuilder.parse(in);
				NodeList root = dom.getFirstChild().getChildNodes();

				pbcur = 40;
				pbinc = 10.0 / (double)root.getLength();
				
				for(Node node1 = root.item(0); node1 != null; node1 = node1.getNextSibling()) {
					String user_name = "\"NULL\"", realname = "\"NULL\"", user_status = "\"NULL\"";
					user_id = "";
					pbcur += pbinc;
					
					for(Node node2 = node1.getFirstChild(); node2 != null; node2 = node2.getNextSibling()) {
						if(node2.getNodeName() == "user_id") {
							user_id = node2.getTextContent();
						}
						else if(node2.getNodeName() == "user_name") {
							user_name = removeQuotes(node2.getTextContent());
						}
						else if(node2.getNodeName() == "realname") {
							realname = removeQuotes(node2.getTextContent());
						}
						else if(node2.getNodeName() == "status") {
							user_status = removeQuotes(node2.getTextContent());
						}
					}
					
					if(user_id != "") {
						try {
							rs = s.executeQuery("SELECT user_id FROM user WHERE user_id="+user_id);
							if(rs.first())
								s.execute("UPDATE user SET user_name="+user_name+", realname="+realname+", status="+user_status+" WHERE user_id="+user_id);
							else
								s.execute("INSERT INTO user (user_id, user_name, realname, status) VALUES ("+user_id+","+user_name+","+realname+","+user_status+")");
						} catch (SQLException e) {
							System.out.println("SQLException: " + e.getMessage());
							System.out.println("SQLState: " + e.getSQLState());
							System.out.println("VendorError: " + e.getErrorCode());
							System.out.println("INSERT INTO user (user_id, user_name, realname, status) VALUES ("+user_id+","+user_name+","+realname+","+user_status+")");
							return;
						}
						display.syncExec(new Runnable() {
							public void run(){
								pbProgress.setSelection((int)pbcur);
								lblPercent.setText((int)pbcur + "%");
								lblStatus.setText("Processing User: " + user_id);
							}
						});							
					}
				}
			
			} catch (MalformedURLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (ParserConfigurationException e) {
				e.printStackTrace();
			} catch (SAXException e) {
				e.printStackTrace();
			}
		
			display.syncExec(new Runnable() {
				public void run(){
					browser.setText(generateIssueQuery());
					lblStatus.setText("Querying Issues");
				}
			});											
		}
	}
	
	private static class processIssueQuery implements Runnable {
		double pbinc, pbcur;
		String issue_id;
		
		public void run() {
			try {
				InputStream in = new URL("http://zerlot.cse.nd.edu/qresult/"+sfUsername+"/"+sfUsername+".xml").openStream();
				DocumentBuilder dombuilder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
				Document dom = dombuilder.parse(in);
				NodeList root = dom.getFirstChild().getChildNodes();

				pbcur = 50;
				pbinc = 50.0 / (double)root.getLength();

				for(Node node1 = root.item(0); node1 != null; node1 = node1.getNextSibling()) {
					String project_issue_id = "", category_id = "", category_name = "\"NULL\"", issue_project_list_name = "";
					String issue_project_list_description = "\"NULL\"", status_id = "", status_name = "\"NULL\"", details = "\"NULL\"", resolution_id = "";
					String resolution_name = "\"NULL\"", priority = "", submitted_by = "", assigned_to = "", open_date = "", close_date = "";
					issue_id = "";
					pbcur += pbinc;

					for(Node node2 = node1.getFirstChild(); node2 != null; node2 = node2.getNextSibling()) {
						if(node2.getNodeName() == "group_artifact_id") {
							project_issue_id = node2.getTextContent();
						}
						else if(node2.getNodeName() == "artifact_group_list_name") {
							issue_project_list_name = node2.getTextContent();
						}
						else if(node2.getNodeName() == "artifact_group_list_description") {
							issue_project_list_description = node2.getTextContent();
						}
						else if(node2.getNodeName() == "artifact_id") {
							issue_id = node2.getTextContent();
						}
						else if(node2.getNodeName() == "status_id") {
							status_id = node2.getTextContent();
						}
						else if(node2.getNodeName() == "status_name") {
							status_name = node2.getTextContent();
						}
						else if(node2.getNodeName() == "resolution_id") {
							resolution_id = node2.getTextContent();
						}
						else if(node2.getNodeName() == "resolution_name") {
							resolution_name = node2.getTextContent();
						}
						else if(node2.getNodeName() == "category_id") {
							category_id = node2.getTextContent();
						}
						else if(node2.getNodeName() == "category_name") {
							category_name = node2.getTextContent();
						}
						else if(node2.getNodeName() == "priority") {
							priority = node2.getTextContent();
						}
						else if(node2.getNodeName() == "submitted_by") {
							submitted_by = node2.getTextContent();
						}
						else if(node2.getNodeName() == "assigned_to") {
							assigned_to = node2.getTextContent();
						}
						else if(node2.getNodeName() == "open_date") {
							open_date = node2.getTextContent();
						}
						else if(node2.getNodeName() == "close_date") {
							close_date = node2.getTextContent();
						}
						else if(node2.getNodeName() == "details") {
							details = removeQuotes(node2.getTextContent());
						}
					}
				
					if(issue_id != "") {
						try {
							rs = s.executeQuery("SELECT project_issue_id FROM issue_project_list WHERE project_issue_id="+project_issue_id);
							if(!rs.first())
								s.execute("INSERT INTO issue_project_list (project_issue_id, project_id, name, description) VALUES ("+project_issue_id+","+projectID+","+issue_project_list_name+","+issue_project_list_description+")");

							rs = s.executeQuery("SELECT id FROM issue_status WHERE id="+status_id);
							if(!rs.first())
								s.execute("INSERT INTO issue_status (id, status_name) VALUES ("+status_id+","+status_name+")");

							rs = s.executeQuery("SELECT id FROM issue_resolution WHERE id="+resolution_id);
							if(!rs.first())
								s.execute("INSERT INTO issue_resolution (id, resolution_name) VALUES ("+resolution_id+","+resolution_name+")");

							rs = s.executeQuery("SELECT id FROM issue_category WHERE id="+category_id);
							if(!rs.first())
								s.execute("INSERT INTO issue_category (id, category_name) VALUES ("+category_id+","+category_name+")");

							rs = s.executeQuery("SELECT issue_id FROM issue WHERE issue_id="+issue_id);
							if(!rs.first())
								s.execute("INSERT INTO issue (issue_id, project_issue_id, status_id, resolution_id, category_id, priority, submitted_by, assigned_to, open_date, close_date, details) VALUES ("+issue_id+","+project_issue_id+","+status_id+","+resolution_id+","+category_id+","+priority+","+submitted_by+","+assigned_to+","+open_date+","+close_date+","+details+")");
						} catch (SQLException e) {
							System.out.println("SQLException: " + e.getMessage());
							System.out.println("SQLState: " + e.getSQLState());
							System.out.println("VendorError: " + e.getErrorCode());
							System.out.println("INSERT INTO issue_project_list (project_issue_id, project_id, name, description) VALUES ("+project_issue_id+","+projectID+","+issue_project_list_name+","+issue_project_list_description+")");
							System.out.println("INSERT INTO issue_status (id, status_name) VALUES ("+status_id+","+status_name+")");
							System.out.println("INSERT INTO issue_resolution (id, resolution_name) VALUES ("+resolution_id+","+resolution_name+")");
							System.out.println("INSERT INTO issue_category (id, category_name) VALUES ("+category_id+","+category_name+")");
							System.out.println("INSERT INTO issue (issue_id, project_issue_id, status_id, resolution_id, category_id, priority, submitted_by, assigned_to, open_date, close_date, details) VALUES ("+issue_id+","+project_issue_id+","+status_id+","+resolution_id+","+category_id+","+priority+","+submitted_by+","+assigned_to+","+open_date+","+close_date+","+details+")");
						}

						display.syncExec(new Runnable() {
							public void run(){
								pbProgress.setSelection((int)pbcur);
								lblPercent.setText((int)pbcur + "%");
								lblStatus.setText("Processing Issue: " + issue_id);
							}
						});							
					}
				}
			
			} catch (MalformedURLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (ParserConfigurationException e) {
				e.printStackTrace();
			} catch (SAXException e) {
				e.printStackTrace();
			}

			reset();
		}
	}
	
	private static void reset() {
		status = 0;
		allowExit = true;
		
		try {
			rs = null;
			s.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		display.syncExec(new Runnable() {
			public void run(){
				pbProgress.setSelection(0);
				lblPercent.setText("");
				lblStatus.setText("");
				txtProjectName.setEnabled(true);
				txtSFPassword.setEnabled(true);
				txtSFUsername.setEnabled(true);
				txtSQLDomain.setEnabled(true);
				txtSQLPassword.setEnabled(true);
				txtSQLUsername.setEnabled(true);
				dtMonth.setEnabled(true);
				btnDump.setEnabled(true);
				btnDrop.setEnabled(true);
				browser.setText("");
			}
		});			
	}
	
	private static String removeQuotes(String in) {
		if(in.length() <= 3)
			if(!in.contains("\""))
				return "\""+in+"\"";
			else
				return in;
		
		if(in.contains("\""))
			in = in.substring(1, in.length()-2);
		
		for(int i = in.indexOf("\\"); i != -1; i = in.indexOf("\\", i+2))
			in = in.substring(0, i) + "\\\\" + in.substring(i+1, in.length());
		for(int i = in.indexOf("\""); i != -1; i = in.indexOf("\"", i+2))
			in = in.substring(0, i) + "\\\"" + in.substring(i+1, in.length());
		for(int i = in.indexOf("\'"); i != -1; i = in.indexOf("\'", i+2))
			in = in.substring(0, i) + "\\\'" + in.substring(i+1, in.length());
		in = "\""+in+"\"";
		return in;
	}		
}